import datetime
import pymysql
import requests
import json

from dbutils.pooled_db import PooledDB


# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# 记录 数据分析-主动服务-开机率对比 analysis_startup_rate  未安装生活助手APK 数据来源于大数据
def insertStartupRate(date,crowdId):

    #开机率
    activeRate = 0
    url = "http://192.168.2.177/prod-api/star-rocks/api/v1/query-service"
    payload = json.dumps({
        "modelId": "star-model-CalculationCrowdStartupRate",
        "requireParams": {
            "corwd_id": crowdId
        }
    })
    headers = {
        'Content-Type': 'application/json'
    }
    try:
        conn, cur = getDataConnection()
        # 调用大数据接口获取昨天的 该人群的开机率
        response = requests.request("POST", url, headers=headers, data=payload)

        print('大数据接口返回的结果%s'% response.text)
        responseData = json.loads(response.text)
        if responseData['code'] == 200:
            data = responseData['data']
            if data and len(data):
                crowdData = data[0]
                if crowdId == crowdData['crowd_id']:
                    # 获取开机率
                    activeRate = crowdData['active_rate']

        # 判断 analysis_startup_rate 表是否已经存在当天 开机率数据
        selectCountSql = """
                            select count(1) num from analysis_startup_rate t where 1=1 and t.ref_date =  '%s' and crowd_id = %s and install_flag =0
                        """ % (date,crowdId)
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        if countNum == 0:
            print ("%s日新增%s人群,开机率为%s" % (date,crowdId,activeRate))
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_startup_rate(startup_rate,ref_date,crowd_id,install_flag) 
                value(%s,'%s',%s,%s)""" % (activeRate,date,crowdId,0)
            cur.execute(insertSql)
        else:
            print ("%s日更新%s人群,开机率为%s" % (date,crowdId,activeRate))
            # 更新早间服务可见设备量
            updateSql = """
                update analysis_startup_rate set startup_rate = %s ,update_time = now()  where
                    ref_date = '%s' and crowd_id = 1
            """ % (activeRate,date);
            cur.execute(updateSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

if __name__ == '__main__':
    #today = todayYMD()
    date = getdate(1)
    #today = "2021-12-13"
    print ("%s 日期,执行<analysis_startup_rate>报表" % date)
    insertStartupRate(date,111367)
    #insertStartupRateCrowdId(date)